package com.xxxt.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import com.xxxt.entity.Student;

public class ExportExcelUtil {
	public static String ExportExcel(List<Student> studentList,HttpServletRequest request) {
		//传入list解析导出
		// 创建一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个工作表
        HSSFSheet sheet = workbook.createSheet("实习学生表1");
        
        for(int i=0;i<20;i++) {
        	sheet.setColumnWidth(i,6000);
        }
        // 添加表头行
        HSSFRow hssfRow = sheet.createRow(0);
        hssfRow.setHeightInPoints(18);
        // 设置单元格格式居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 添加表头内容
        HSSFCell headCell = hssfRow.createCell(0);
        headCell.setCellValue("学号");
        headCell.setCellStyle(cellStyle);
 
        headCell = hssfRow.createCell(1);
        headCell.setCellValue("姓名");
        headCell.setCellStyle(cellStyle);
 
        headCell = hssfRow.createCell(2);
        headCell.setCellValue("班级");//需要拼接
        headCell.setCellStyle(cellStyle);
        
        headCell = hssfRow.createCell(3);
        headCell.setCellValue("专业");
        headCell.setCellStyle(cellStyle);
        
        headCell = hssfRow.createCell(4);
        headCell.setCellValue("学院");
        headCell.setCellStyle(cellStyle);
        
        headCell = hssfRow.createCell(5);
        headCell.setCellValue("学校");
        headCell.setCellStyle(cellStyle);
 
        // 添加数据内容
        for (int i = 0; i < studentList.size(); i++) {
            hssfRow = sheet.createRow((int) i + 1);
            Student student = studentList.get(i);
 
            // 创建单元格，并设置值
            HSSFCell cell = hssfRow.createCell(0);
            cell.setCellValue(student.getSno());
            cell.setCellStyle(cellStyle);
 
            cell = hssfRow.createCell(1);
            cell.setCellValue(student.getName());
            cell.setCellStyle(cellStyle);
            
            cell = hssfRow.createCell(2);
            cell.setCellValue(student.getClassGrade().getGrade().getGrade()+student.getClassGrade().getClassGrade());
            cell.setCellStyle(cellStyle);
            
            cell = hssfRow.createCell(3);
            cell.setCellValue(student.getClassGrade().getGrade().getSpecialty().getSpecialtyName());
            cell.setCellStyle(cellStyle);
            
            cell = hssfRow.createCell(4);
            cell.setCellValue(student.getClassGrade().getGrade().getSpecialty().getCollege().getCollegeName());
            cell.setCellStyle(cellStyle);
            
            cell = hssfRow.createCell(5);
            cell.setCellValue(student.getClassGrade().getGrade().getSpecialty().getCollege().getSchool().getSchoolName());
            cell.setCellStyle(cellStyle);
        }
        // 保存Excel文件
        String path=null;
        try {
        	long currentTimeMillis = System.currentTimeMillis();
        	String string = Long.toString(currentTimeMillis);
        	String substring = string.substring(string.length()-5, string.length());
        	String dirpath= request.getServletContext().getRealPath("/shixi/");
        	new File(dirpath).mkdirs();
        	 path = request.getServletContext().getRealPath("/shixi/"+substring+"_student.xls");
            OutputStream outputStream = new FileOutputStream(path);
            workbook.write(outputStream);
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
		return path;
	}

}
